/**
 * Copyright (C) 2018-2020
 * All rights reserved, Designed By www.yixiang.co
 * 注意：
 * 本软件为www.yixiang.co开发研制，未经购买不得使用
 * 购买后可获得全部源代码（禁止转卖、分享、上传到码云、github等开源平台）
 * 一经发现盗用、分享等行为，将追究法律责任，后果自负
 */
package co.yixiang.modules.user.service.mapper;

import co.yixiang.common.mapper.CoreMapper;
import co.yixiang.modules.user.domain.YxUser;
import co.yixiang.modules.user.service.dto.PromUserDto;
import co.yixiang.modules.user.service.dto.YxUserDto;
import co.yixiang.modules.user.vo.TotalDataQueryVo;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
* @author hupeng
* @date 2020-05-12
*/
@Repository
public interface UserMapper extends CoreMapper<YxUser> {


    @Select("<script>SELECT u.uid,u.nickname,u.avatar,DATE_FORMAT(u.create_time,'%Y/%m/%d %H:%i:%s') as time," +
            "u.spread_count as childCount,COUNT(o.id) as orderCount," +
            "IFNULL(SUM(o.pay_price),0) as numberCount,u.level,u.phone FROM yx_user u " +
            "LEFT JOIN yx_store_order o ON u.uid=o.uid " +
            "WHERE u.uid in <foreach item='id' index='index' collection='uids' " +
            " open='(' separator=',' close=')'>" +
            "   #{id}" +
            " </foreach> <if test='keyword != null'>" +
            " AND ( u.nickname LIKE CONCAT(CONCAT('%',#{keyword}),'%') OR u.phone LIKE CONCAT(CONCAT('%',#{keyword}),'%'))</if>" +
            " GROUP BY u.uid ORDER BY #{orderByStr} " +
            "</script>")
    List<PromUserDto> getUserSpreadCountList(Page page,
                                             @Param("uids") List uids,
                                             @Param("keyword") String keyword,
                                             @Param("orderByStr") String orderBy);

    @Select("<script>SELECT u.uid,u.nickname,u.avatar,DATE_FORMAT(u.create_time,'%Y/%m/%d %H:%i:%s') as time," +
            "u.spread_count as childCount,COUNT(o.id) as orderCount," +
            "IFNULL(SUM(o.pay_price),0) as numberCount,u.level,u.phone,u.is_open_lian as isOpenLian,u.is_open_crown as isOpenCrown FROM yx_user u " +
            "LEFT JOIN yx_store_order o ON u.uid=o.uid " +
            "WHERE u.uid in <foreach item='id' index='index' collection='uids' " +
            " open='(' separator=',' close=')'>" +
            "   #{id}" +
            " </foreach> <if test='keyword != null'>" +
            " AND ( u.nickname LIKE CONCAT(CONCAT('%',#{keyword}),'%') OR u.phone LIKE CONCAT(CONCAT('%',#{keyword}),'%'))</if>" +
            "<if test='level != 7'>" +
            "  AND  u.level =#{level}</if>"+
            " GROUP BY u.uid ORDER BY #{orderByStr} " +
            "</script>")
    List<PromUserDto> getUserSpreadCountList4new(Page page,
                                             @Param("uids") List uids,
                                             @Param("keyword") String keyword,
                                             @Param("orderByStr") String orderBy,@Param("level") Integer level);

    @Update("update yx_user set now_money=now_money-#{payPrice}" +
            " where uid=#{uid}")
    int decPrice(@Param("payPrice") BigDecimal payPrice, @Param("uid") Long uid);
//
//    @Update("update yx_user set brokerage_price=brokerage_price+#{brokeragePrice}" +
//            " where uid=#{uid}")
//    int incBrokeragePrice(@Param("brokeragePrice") double brokeragePrice,@Param("uid") int uid);

    @Update("update yx_user set pay_count=pay_count+1" +
            " where uid=#{uid}")
    int incPayCount(@Param("uid") Long uid);

    @Update("update yx_user set prize_count=prize_count+#{prizeCount}" +
            " where uid=#{uid}")
    int incPrizeCount(@Param("uid") Long uid,@Param("prizeCount") int prizeCount);

    @Update("update yx_user set prize_count=0 where prize_count !=0")
    int updatePrizeCount();

    @Update("update yx_prize_record set prize_status=2 where prize_status=0 and prize_id=#{prizeId}")
    int updatePrizeStatus(@Param("prizeId") Long prizeId);

    @Update("update yx_user set now_money=now_money+#{price}" +
            " where uid=#{uid}")
    int incMoney(@Param("uid") Long uid,BigDecimal price);

    @Update("update yx_user set integral=integral-#{integral}" +
            " where uid=#{uid}")
    int decIntegral(@Param("integral") double integral,@Param("uid") Long uid);

    @Update("update yx_user set integral=integral+#{integral}" +
            " where uid=#{uid}")
    int incIntegral(@Param("integral") double integral,@Param("uid") Long uid);

    @Update( "update yx_user set status = #{status} where uid = #{id}")
    void updateOnstatus(@Param("status") Integer status, @Param("id") Long id);

    @Update( "update yx_user set now_money = now_money + ${money} where uid = #{id}")
    void updateMoney(@Param("money") double money, @Param("id") long id);

    @Update("update yx_user set brokerage_price = brokerage_price+ ${price} where uid = #{id}")
    void incBrokeragePrice(@Param("price") BigDecimal price,@Param("id") Long id);

    @Update("update yx_user set balance_sum = balance_sum+ ${price} where uid = #{id}")
    void incBalancePrice(@Param("price") BigDecimal price,@Param("id") Long id);

    @Update("update yx_user set balance_sum = balance_sum- ${price} where uid = #{id}")
    void decBalancePrice(@Param("price") BigDecimal price,@Param("id") Long id);

    @Select("SELECT T2.uid,T2.spread_uid,T2.userName,T2.level,T2.spread_count,T2.normal_count,T2.vip_count,T2.whether_slim " +
            "FROM (" +
            "SELECT " +
            "@r AS _id," +
            "(SELECT @r := spread_uid FROM yx_user WHERE uid = _id) AS spread_uid," +
            "@l := @l + 1 AS lvl " +
            "FROM" +
            "(SELECT @r := #{uid}, @l := 0) vars," +
            "yx_user h " +
            "WHERE @r <> 0) T1 " +
            "JOIN yx_user T2 " +
            "ON T1._id = T2.uid " +
            "WHERE T2.uid<>#{uid} " +
            "ORDER BY T1.lvl ASC " )
    List<HashMap<String,Object>> getParentUsers(@Param("uid") Long uid);

    @Select("SELECT T2.uid AS parent_id,T2.level AS member_id " +
            "FROM (" +
            "SELECT " +
            "@r AS _id," +
            "(SELECT @r := spread_uid FROM yx_user WHERE uid = _id) AS spread_uid," +
            "@l := @l + 1 AS lvl " +
            "FROM" +
            "(SELECT @r := #{uid}, @l := 0) vars," +
            "yx_user h " +
            "WHERE @r <> 0) T1 " +
            "JOIN yx_user T2 " +
            "ON T1._id = T2.uid " +
            "WHERE T2.uid<>#{uid} AND T2.level >=#{level} " +
            "AND T2.whether_vip_slim ='Y' " +
            "ORDER BY T1.lvl ASC LIMIT 1 " )
    List<HashMap<String,Object>> getParentByUserid(@Param("uid") Long uid,@Param("level") Integer level);

    @Select("SELECT T2.uid AS parent_id,T2.level AS member_id " +
            "FROM (" +
            "SELECT " +
            "@r AS _id," +
            "(SELECT @r := spread_uid FROM yx_user WHERE uid = _id) AS spread_uid," +
            "@l := @l + 1 AS lvl " +
            "FROM" +
            "(SELECT @r := #{uid}, @l := 0) vars," +
            "yx_user h " +
            "WHERE @r <> 0) T1 " +
            "JOIN yx_user T2 " +
            "ON T1._id = T2.uid " +
            "WHERE T2.uid<>#{uid} AND T2.level=#{level} AND T2.is_open_crown='Y' " +
            "ORDER BY T1.lvl ASC LIMIT 4" )
    List<HashMap<String,Object>> getParentByUserId(@Param("uid") Long uid,@Param("level") Integer level);

    @Select("SELECT T2.uid AS parent_id,T2.level AS member_id " +
            "FROM (" +
            "SELECT " +
            "@r AS _id," +
            "(SELECT @r := spread_uid FROM yx_user WHERE uid = _id) AS spread_uid," +
            "@l := @l + 1 AS lvl " +
            "FROM" +
            "(SELECT @r := #{uid}, @l := 0) vars," +
            "yx_user h " +
            "WHERE @r <> 0) T1 " +
            "JOIN yx_user T2 " +
            "ON T1._id = T2.uid " +
            "WHERE T2.uid<>#{uid} AND T2.level=#{level} AND T2.is_open_crown='Y' " +
            "ORDER BY T1.lvl ASC" )
    List<HashMap<String,Object>> getParentByBossUserId(@Param("uid") Long uid,@Param("level") Integer level);

    @Select("select count(*) from " +
            " yx_user t " +
            "WHERE t.spread_uid=#{uid} AND t.level >=#{level} " )
    String getChildrens4Main(@Param("uid") Long uid,@Param("level") Integer level);

    @Update("update yx_user SET path = (SELECT a.b FROM (SELECT CONCAT(',', GROUP_CONCAT(T2.uid),',') AS b " +
            "FROM (" +
            "SELECT " +
            "@r AS _id," +
            "(SELECT @r := spread_uid FROM yx_user WHERE uid = _id) AS spread_uid," +
            "@l := @l + 1 AS lvl " +
            "FROM" +
            "(SELECT @r := #{uid}, @l := 0) vars," +
            "yx_user h " +
            "WHERE @r <> 0) T1 " +
            "JOIN yx_user T2 " +
            "ON T1._id = T2.uid " +
            "WHERE T2.uid<>#{uid} " +
            "ORDER BY T1.lvl ASC) a) "+
            " where uid=#{uid}")
    void initPath(@Param("uid") Long uid);

    @Select("SELECT  'under_count' AS TYPE,COUNT(*) AS COUNT FROM yx_user WHERE spread_uid=${uid}  " +
            "UNION ALL "+
            "SELECT  'normal_count' AS TYPE,COUNT(*) AS COUNT FROM yx_user a WHERE a.path  LIKE '%,${uid},%' AND a.level =0 " +
            "UNION ALL "+
            "SELECT  'total_count' AS TYPE,COUNT(*) AS COUNT FROM yx_user a WHERE a.path  LIKE '%,${uid},%' " +
            "UNION ALL "+
            "SELECT a.TYPE,IFNULL(SUM(a.COUNT),0) AS COUNT from("+
            "SELECT 'total_order_count' as TYPE,IFNULL(SUM(d.cart_num),0) AS COUNT FROM  (SELECT c.cart_num*IFNULL(singular,0) AS cart_num  " +
            "FROM yx_store_order_cart_info a LEFT JOIN yx_store_product b ON b.id =a.product_id "+
            "LEFT JOIN yx_store_cart c ON c.id =a.cart_id " +
            "WHERE b.is_vip =1 "+
            "AND a.oid IN ( SELECT id FROM yx_store_order WHERE paid =1  "+
            "AND (uid=${uid} OR uid IN(SELECT uid FROM yx_user WHERE path  LIKE '%,${uid},%'))))d  "+
            "UNION ALL "+
            "SELECT 'total_order_count' AS TYPE,IFNULL(base_count,0) AS COUNT FROM  yx_user WHERE uid=${uid})a group by a.TYPE "+
            "UNION ALL "+
            "SELECT 'vip_hd_count' AS TYPE,IFNULL(SUM(d.cart_num),0) AS COUNT FROM  (SELECT CASE WHEN b.price =299 THEN c.cart_num ELSE c.cart_num END AS cart_num  " +
            "FROM yx_store_order_cart_info a LEFT JOIN yx_store_product b ON b.id =a.product_id "+
            "LEFT JOIN yx_store_cart c ON c.id =a.cart_id " +
            "WHERE b.is_vip =1 "+
            "AND a.oid IN ( SELECT id FROM yx_store_order WHERE paid =1 AND DATE_FORMAT(create_time,'%Y-%m-%d')>='2020-10-01'  "+
            "AND (uid=${uid} OR uid IN(SELECT uid FROM yx_user WHERE path  LIKE '%,${uid},%'))))d  "+
            "UNION ALL "+
            "SELECT 'vip_directly_count' AS TYPE,IFNULL(SUM(d.cart_num),0) AS COUNT FROM  (SELECT c.cart_num*IFNULL(singular,0) AS cart_num  " +
            "FROM yx_store_order_cart_info a LEFT JOIN yx_store_product b ON b.id =a.product_id "+
            "LEFT JOIN yx_store_cart c ON c.id =a.cart_id " +
            "WHERE b.is_vip =1 "+
            "AND a.oid IN ( SELECT id FROM yx_store_order yo LEFT JOIN yx_user yu ON yu.uid =yo.uid WHERE yo.paid =1 AND DATE_FORMAT(yo.create_time,'%Y-%m-%d')>='2021-01-01' "+
            "AND yu.spread_uid=${uid}))d" +
            " UNION ALL "+
            "SELECT 'vip_total_sum' AS TYPE,IFNULL(sum(c.total_price),0) AS COUNT " +
            " FROM yx_store_order_cart_info a " +
            " LEFT JOIN yx_store_cart d ON d.id =a.cart_id " +
            " LEFT JOIN yx_store_product b ON b.id =a.product_id " +
            " LEFT JOIN yx_store_order c ON a.oid = c.id " +
            " LEFT JOIN yx_user yu ON yu.uid =c.uid " +
            " WHERE yu.spread_uid=#{uid} and c.is_del =0 and c.paid=1" +
            " UNION ALL "+
            "SELECT 'activity_directly_count' AS TYPE,IFNULL(SUM(d.cart_num),0) AS COUNT FROM  (SELECT c.cart_num*IFNULL(singular,0) AS cart_num  " +
            "FROM yx_store_order_cart_info a LEFT JOIN yx_store_product b ON b.id =a.product_id "+
            "LEFT JOIN yx_store_cart c ON c.id =a.cart_id " +
            "WHERE b.is_free !=1 "+
            "AND a.oid IN ( SELECT id FROM yx_store_order yo LEFT JOIN yx_user yu ON yu.uid =yo.uid WHERE yo.paid =1 AND DATE_FORMAT(yo.create_time,'%Y-%m-%d')>=#{avtivityBeginDate} AND DATE_FORMAT(yo.create_time,'%Y-%m-%d')<=#{avtivityEndDate}"+
            " AND yu.spread_uid=${uid})" +
            ")d ")
    List<HashMap<String, Object>> getChildrensCount(Long uid,@Param("avtivityBeginDate") String avtivityBeginDate,@Param("avtivityEndDate") String avtivityEndDate);

    @Update("update yx_user set vip_hrough_train = vip_hrough_train+ ${vipHroughTrain},gold_hrough_train = gold_hrough_train+ ${goldHroughTrain},diamonds_hrough_train = diamonds_hrough_train+ ${diamondsHroughTrain} where uid = #{id}")
    void updateThroughInc(@Param("vipHroughTrain") Integer vipHroughTrain,@Param("goldHroughTrain") Integer goldHroughTrain,@Param("diamondsHroughTrain") Integer diamondsHroughTrain,@Param("id") Long id);

    @Update("update yx_user set vip_hrough_train = vip_hrough_train- ${vipHroughTrain},gold_hrough_train = gold_hrough_train- ${goldHroughTrain},diamonds_hrough_train = diamonds_hrough_train- ${diamondsHroughTrain} where uid = #{id}")
    void updateThroughDec(@Param("vipHroughTrain") Integer vipHroughTrain,@Param("goldHroughTrain") Integer goldHroughTrain,@Param("diamondsHroughTrain") Integer diamondsHroughTrain,@Param("id") Long id);


    @Select("SELECT a.monthArr,SUM(a.orderArr) AS orderArr ,SUM(a.turnoverArr) AS turnoverArr,SUM(a.userArr) AS userArr FROM(   " +
            "SELECT DATE_FORMAT(b.create_time,'%Y-%m') AS monthArr,IFNULL(SUM(c.cart_num),0) AS orderArr , " +
            " IFNULL(SUM(b.pay_price),0) AS turnoverArr ,0 AS userArr " +
            " FROM yx_store_order_cart_info a "+
            " LEFT JOIN yx_store_order b ON b.id =a.oid " +
            "LEFT JOIN yx_store_cart c ON c.id =a.cart_id "+
            "WHERE b.paid =1 AND b.is_del =0 AND DATE_FORMAT(b.create_time,'%Y-%m')>=#{beginMonth}  " +
            " AND DATE_FORMAT(b.create_time,'%Y-%m')<=#{endMonth} "+
            " GROUP BY DATE_FORMAT(b.create_time,'%Y-%m')  "+
            "UNION ALL "+
            " SELECT DATE_FORMAT(a.create_time,'%Y-%m') AS monthArr,0 AS orderArr , "+
            "0 AS turnoverArr ,IFNULL(COUNT(a.uid),0) AS userArr "+
            "FROM yx_user a " +
            " WHERE a.is_del =0 AND DATE_FORMAT(a.create_time,'%Y-%m')>=#{beginMonth} "+
            "AND DATE_FORMAT(a.create_time,'%Y-%m')<=#{endMonth} " +
            "GROUP BY DATE_FORMAT(a.create_time,'%Y-%m'))a "+
            "GROUP BY a.monthArr  "+
            "ORDER BY a.monthArr ASC  ")
    List<TotalDataQueryVo> totalDatas(@Param("beginMonth") String beginMonth, @Param("endMonth") String endMonth);

    @Update("update yx_user set vip_hrough_train = vip_hrough_train+ #{giveActivityCount},is_through='Y' where uid = #{id}")
    void incGiveVip(@Param("id") Long id,@Param("giveActivityCount") Integer giveActivityCount);

    @Select("<script> select * " +
            "from yx_user u where 1=1  "  +
            "<if test =\"isOpen !=null\">and u.is_Open=#{isOpen}</if> " +
            "<if test =\"userType !=''\">and u.user_Type=#{userType}</if> " +
            "<if test =\"phone !=null\">and u.phone  LIKE CONCAT('%',#{phone},'%')</if> " +
            "<if test =\"isPromoter !=null\">and u.is_Promoter=#{isPromoter}</if> " +
            "<if test =\"grade !=null\">and u.grade=#{grade}</if> " +
            "<if test =\"level !=null\">and u.level=#{level}</if> " +
            "<if test =\"uid !=null\">and u.uid=#{uid}</if> " +
            "<if test =\"nickname !=null\">and u.nickname LIKE CONCAT('%',#{nickname},'%')</if> order by u.create_time desc </script> ")
    List<YxUser> findAllUserByQueryCriteria(@Param("nickname") String nickname,@Param("phone") String phone, @Param("isPromoter") Integer isPromoter, @Param("userType") String userType, @Param("isOpen") String isOpen, @Param("uid") Long uid, @Param("grade") Integer grade, @Param("level") Integer level);

}
